unit uFrmImportaTXT;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBClient, StdCtrls, Mask, JvExMask, JvToolEdit, Grids, DBGrids,
  ExtCtrls, ComCtrls, DBCtrls, FMTBcd, SqlExpr, MemDS, DBAccess, Uni;

type
  TFrmImportaReducao = class(TForm)
    dsRegC400: TDataSource;
    dsRegC405: TDataSource;
    dsRegC420: TDataSource;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Button1: TButton;
    Button2: TButton;
    cdsRegC400: TClientDataSet;
    cdsRegC400COD_MOD: TStringField;
    cdsRegC400ECF_MOD: TStringField;
    cdsRegC400ECF_FAB: TStringField;
    cdsRegC400ECF_CX: TStringField;
    cdsRegC405: TClientDataSet;
    cdsRegC405DT_DOC: TStringField;
    cdsRegC405CRO: TStringField;
    cdsRegC405CRZ: TStringField;
    cdsRegC405NUM_COO_FIN: TStringField;
    cdsRegC405GT_FIN: TStringField;
    cdsRegC405VL_BRT: TStringField;
    cdsRegC405ID: TIntegerField;
    cdsRegC405ECF_CX: TStringField;
    cdsRegC420: TClientDataSet;
    cdsRegC420COD_TOT_PAR: TStringField;
    cdsRegC420VLR_ACUM_TOT: TStringField;
    cdsRegC420NR_TOT: TStringField;
    cdsRegC420DESCR_NR_TOT: TStringField;
    cdsRegC420ID_REGC405: TIntegerField;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    DBGrid3: TDBGrid;
    JvFilenameEdit1: TJvFilenameEdit;
    Label1: TLabel;
    RGFiltrar: TRadioGroup;
    TabSheet2: TTabSheet;
    Panel1: TPanel;
    Panel2: TPanel;
    DBGrid4: TDBGrid;
    DBGrid5: TDBGrid;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    EdtCodSitTXT: TEdit;
    EdtCodSitTGA: TEdit;
    EdtPontoDeVenda: TEdit;
    ComboBox1: TComboBox;
    BtnAdicionarCodSit: TButton;
    BtnAdicionarPontodeVenda: TButton;
    cdsRegC400PONTO_DE_VENDA: TStringField;
    cdsRegC405PONTO_DE_VENDA: TStringField;
    cdsRegC420COD_SIT_TGA: TStringField;
    cdsCodSit: TClientDataSet;
    cdsPontoDeVenda: TClientDataSet;
    dsCodSit: TDataSource;
    dsPontoDeVenda: TDataSource;
    cdsCodSitCOD_SIT_TXT: TStringField;
    cdsCodSitCOD_SIT_TGA: TStringField;
    cdsPontoDeVendaNUMERO_SERIE_ECF: TStringField;
    cdsPontoDeVendaCOD_PONTO_VENDA: TStringField;
    DBNavigator1: TDBNavigator;
    DBNavigator2: TDBNavigator;
    UniQuery1: TUniQuery;
    Button3: TButton;
    uqReducao: TUniQuery;
    uqReducaoItem: TUniQuery;
    Edit1: TEdit;
    procedure ImportarArquivoTXT ( CaminhoTXT : string);
    procedure Button1Click(Sender: TObject);
    procedure DBGrid3CellClick(Column: TColumn);
    procedure Button2Click(Sender: TObject);
    procedure DBGrid2CellClick(Column: TColumn);
    procedure dsRegC405DataChange(Sender: TObject; Field: TField);
    procedure dsRegC400DataChange(Sender: TObject; Field: TField);
    procedure ComboBox1Change(Sender: TObject);
    procedure cdsRegC400AfterGetRecords(Sender: TObject;
      var OwnerData: OleVariant);
    procedure cdsRegC400AfterOpen(DataSet: TDataSet);
    procedure BtnAdicionarCodSitClick(Sender: TObject);
    procedure SalvarConfigs;
    procedure CarregarConfigs;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormShow(Sender: TObject);
    procedure BtnAdicionarPontodeVendaClick(Sender: TObject);
    function ExportaParaBD : Boolean;
    function ObterCodReducao : Integer;
    //function ObterValorBruto : Double;
    function ObterValorLiquido : Double;
    function ObterDesconto : Double;
    function ObterCancelamento : Double;
    function ObterCODSIT : string;
    function FormataStringData ( sData : string ) : TDate;
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FrmImportaReducao: TFrmImportaReducao;


implementation

uses uDM;

{$R *.dfm}

{ TFrmImportaReducao }

procedure TFrmImportaReducao.BtnAdicionarCodSitClick(Sender: TObject);
begin
  //ShowMessage(ExtractFilePath(ParamStr(0)));
  cdsCodSit.Insert;
  cdsCodSitCOD_SIT_TXT.Value := EdtCodSitTXT.Text;
  cdsCodSitCOD_SIT_TGA.Value := EdtCodSitTGA.Text;
end;

procedure TFrmImportaReducao.BtnAdicionarPontodeVendaClick(Sender: TObject);
begin
  cdsPontoDeVenda.Insert;
  cdsPontoDeVendaNUMERO_SERIE_ECF.Value := ComboBox1.Text;
  cdsPontoDeVendaCOD_PONTO_VENDA.Value := EdtPontoDeVenda.Text;
end;

procedure TFrmImportaReducao.Button1Click(Sender: TObject);
var i : Integer;
begin
  ImportarArquivoTXT(JvFilenameEdit1.Text);
  ComboBox1.Text := '';
  cdsRegC400.First;
 { for i := 0 to cdsRegC400.RecordCount - 1 do
  begin
    ComboBox1.Items.Add(cdsRegC400ECF_FAB.Value);
    ShowMessage(cdsRegC400ECF_FAB.Value);
    cdsRegC400.Next;
  end;
  cdsRegC400.First; }


end;

procedure TFrmImportaReducao.Button2Click(Sender: TObject);
var i : Integer;
begin
{  cdsRegC405.Filtered := False;
  cdsRegC405.Filter := 'ECF_CX = ' + QuotedStr('003');
  cdsRegC405.Filtered := True;}
  {UniQuery1.Open;
  ShowMessage(IntToStr(UniQuery1.FieldByName('RESULTADO').AsInteger));
  UniQuery1.Close;}
  {cdsRegC420.Filtered := False;
  cdsRegC420.Filter := 'ID_REGC405 = ' + IntToStr(cdsRegC405ID.Value);
  cdsRegC420.Filtered := True;
  cdsRegC420.Locate('COD_TOT_PAR','Can-T',[loCaseInsensitive]); }
{  i := StrToInt(cdsRegC405.FieldByName('CRO').AsString);
  ShowMessage(IntToStr(i));}
end;

procedure TFrmImportaReducao.Button3Click(Sender: TObject);
begin
  ExportaParaBD;
end;

procedure TFrmImportaReducao.CarregarConfigs;
var PathExe : string;
begin
  //PathExe := ExtractFilePath(ParamStr(0));
  if FileExists('cdsCodSit.cds') then cdsCodSit.LoadFromFile('cdsCodSit.cds');
  if FileExists('cdsPontoDeVenda.cds') then cdsPontoDeVenda.LoadFromFile('cdsPontoDeVenda.cds');
  {cdsCodSit.SaveToFile(PathExe + 'cdsCodSit.cds',dfBinary);
  cdsPontoDeVenda.SaveToFile(PathExe + 'cdsPontoDeVenda.cds',dfBinary);}
end;

procedure TFrmImportaReducao.cdsRegC400AfterGetRecords(Sender: TObject;
  var OwnerData: OleVariant);
var i : Integer;
begin

end;

procedure TFrmImportaReducao.cdsRegC400AfterOpen(DataSet: TDataSet);
var i : Integer;
begin


end;

procedure TFrmImportaReducao.ComboBox1Change(Sender: TObject);
var i : Integer;
begin
  {if ComboBox1.ItemIndex = 0 then
  begin
    ComboBox2.Items.Clear;
    for i := 0 to cdsRegC400.FieldCount - 1 do
    begin
      ComboBox2.Text := '';
      ComboBox2.Items.Add(cdsRegC400.Fields[i].DisplayName);
    end;
  end
  else
  if ComboBox1.ItemIndex = 1 then
  begin
    ComboBox2.Items.Clear;
    for i := 0 to cdsRegC405.FieldCount - 1 do
    begin
      ComboBox2.Text := '';
      ComboBox2.Items.Add(cdsRegC405.Fields[i].DisplayName);
    end;
  end
  else
  if ComboBox1.ItemIndex = 2 then
  begin
    ComboBox2.Items.Clear;
    for i := 0 to cdsRegC420.FieldCount - 1 do
    begin
      ComboBox2.Text := '';
      ComboBox2.Items.Add(cdsRegC420.Fields[i].DisplayName);
    end;
  end }


end;

procedure TFrmImportaReducao.DBGrid2CellClick(Column: TColumn);
begin
  cdsRegC420.Filtered := False;
  cdsRegC420.Filter := 'ID_REGC405 = ' + IntToStr(cdsRegC405.FieldByName('ID_REGC405').AsInteger);
  //ShowMessage(cdsRegC420.Filter);
  cdsRegC420.Filtered := True;
end;

procedure TFrmImportaReducao.DBGrid3CellClick(Column: TColumn);
begin
  cdsRegC405.Filtered := False;
  cdsRegC405.Filter := 'ECF_CX = ' + QuotedStr(cdsRegC400ECF_CX.Value);
  cdsRegC405.Filtered := True;
end;

procedure TFrmImportaReducao.dsRegC400DataChange(Sender: TObject;
  Field: TField);
begin
  if RGFiltrar.ItemIndex = 0 then
  begin
    cdsRegC405.Filtered := False;
    cdsRegC405.Filter := 'ECF_CX = ' + QuotedStr(cdsRegC400ECF_CX.Value);
    cdsRegC405.Filtered := True;
  end;


end;

procedure TFrmImportaReducao.dsRegC405DataChange(Sender: TObject;
  Field: TField);
begin
  if RGFiltrar.ItemIndex = 0 then
  begin
    cdsRegC420.Filtered := False;
    cdsRegC420.Filter := 'ID_REGC405 = ' + IntToStr(cdsRegC405.FieldByName('ID_REGC405').AsInteger);
    //ShowMessage(cdsRegC420.Filter);
    cdsRegC420.Filtered := True;
  end;


end;

function TFrmImportaReducao.ExportaParaBD: Boolean;
var vCodSit : string;
 vCRO : Integer;
begin
  uqReducao.Open;
  uqReducaoItem.Open;
  RGFiltrar.ItemIndex := 1;
  //cdsRegC405.Filtered := False;
  cdsRegC405.First;
  //ShowMessage(cdsRegC405.FieldByName('CRO').AsString);
  while not cdsRegC405.Eof do
  begin
      uqReducao.Insert;
      uqReducao.FieldByName('IDREDUCAOZ').Value := ObterCodReducao;
      uqReducao.FieldByName('CODEMPRESA').Value := 1;
      uqReducao.FieldByName('DATAMOVIMENTO').Value := FormataStringData(cdsRegC405DT_DOC.Value);
      uqReducao.FieldByName('CRO').Value := cdsRegC405CRO.AsInteger;
      uqReducao.FieldByName('CRZ').Value := cdsRegC405CRZ.AsInteger;
      uqReducao.FieldByName('NUM_COO_FIN').Value := cdsRegC405NUM_COO_FIN.AsInteger;
      uqReducao.FieldByName('GT_FIN').Value := StrToFloat(cdsRegC405GT_FIN.Value);
      uqReducao.FieldByName('VLR_BRT').Value := StrToFloat(cdsRegC405VL_BRT.Value);
      uqReducao.FieldByName('TOTALDESCONTOS').Value := ObterDesconto;
      uqReducao.FieldByName('TOTALCANCELAMENTOS').Value := ObterCancelamento;
      uqReducao.FieldByName('VALORCONTABIL').Value := ObterValorLiquido;
      uqReducao.FieldByName('DATAREDUCAO').Value := FormataStringData(cdsRegC405DT_DOC.Value);
      uqReducao.FieldByName('CODPONTOVENDA').Value := cdsRegC405PONTO_DE_VENDA.Value;
      uqReducao.Post;

    cdsRegC420.Filtered := False;
    cdsRegC420.Filter := 'ID_REGC405 = ' + IntToStr(cdsRegC405.FieldByName('ID_REGC405').AsInteger);
    cdsRegC420.Filtered := True;
    cdsRegC420.First;
    while not cdsRegC420.Eof do
    begin
      vCodSit := ObterCODSIT;
      //if vCodSit = 'NI' then ShowMessage('oi');
      if vCodSit <> '' then
      with uqReducaoItem do
      begin
        Insert;
        FieldByName('IDREDUCAOZ').Value := uqReducao.FieldByName('IDREDUCAOZ').Value;
        FieldByName('CODEMPRESA').Value := 1;
        FieldByName('CODSIT').Value := vCodSit;
        FieldByName('CODPONTOVENDA').Value := uqReducao.FieldByName('CODPONTOVENDA').Value;
        FieldByName('DATAMOVIMENTO').Value := uqReducao.FieldByName('DATAMOVIMENTO').Value;
        FieldByName('TOTALPARCIAL').Value := StrToFloat(cdsRegC420VLR_ACUM_TOT.Value);
        Post;
        //ShowMessage('INSERIDO');
      end;
      cdsRegC420.Next;
    end;
    cdsRegC405.Next;
  end;
  uqReducao.Close;
  uqReducaoItem.Close;

end;



function TFrmImportaReducao.FormataStringData(sData: string): TDate;
begin
  sData := StringReplace(sData,'.','/',[rfReplaceAll]);
  Result := StrToDate(sData);
end;

procedure TFrmImportaReducao.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  SalvarConfigs;
end;

procedure TFrmImportaReducao.FormShow(Sender: TObject);
begin
  CarregarConfigs;
end;

procedure TFrmImportaReducao.ImportarArquivoTXT(CaminhoTXT: string);
var vArquivo , vRegC400 , vRegC405 , vRegC420 , vTemp : TStringList;
  vCx , vData  : string;
  i , vIDC405 : Integer;

begin
  vArquivo := TStringList.Create;
  vTemp := TStringList.Create;
  vTemp.StrictDelimiter := True;
  vTemp.Delimiter := '|';

  vRegC400 := TStringList.Create;
  vRegC400.StrictDelimiter := True;
  vRegC400.Delimiter := '|';

  vRegC405 := TStringList.Create;
  vRegC405.StrictDelimiter := True;
  vRegC405.Delimiter := '|';

  vRegC420 := TStringList.Create;
  vRegC420.StrictDelimiter := True;
  vRegC420.Delimiter := '|';

  vArquivo.LoadFromFile(CaminhoTXT);
  for i := 0 to vArquivo.Count - 1 do
  begin
    vTemp.Clear;
    vTemp.DelimitedText := vArquivo[i];
    //ShowMessage(vTemp.DelimitedText);
    if vTemp[1] = 'C400' then
    begin
      cdsRegC400.Insert;
      cdsRegC400COD_MOD.Value := vTemp[2];
      cdsRegC400ECF_MOD.Value := vTemp[3];
      cdsRegC400ECF_FAB.Value := vTemp[4];
      if cdsPontoDeVenda.Locate('NUMERO_SERIE_ECF',cdsRegC400ECF_FAB.Value,[loCaseInsensitive]) then
        cdsRegC400PONTO_DE_VENDA.Value := cdsPontoDeVendaCOD_PONTO_VENDA.Value;
      cdsRegC400ECF_CX.Value  := vTemp[5];
      vCx := cdsRegC400ECF_CX.Value;
      //cdsRegC400.Post;
    end
    else
    if vTemp[1] = 'C405' then
    begin
      cdsRegC405.Insert;
      vIDC405 := vIDC405 + 1;
      vData := vTemp[2];
      insert('.',vData,3);
      insert('.',vData,6);
      cdsRegC405DT_DOC.Value :=  vData; //vTemp[2];
      cdsRegC405CRO.Value := vTemp[3];
      cdsRegC405CRZ.Value := vTemp[4];
      cdsRegC405NUM_COO_FIN.Value := vTemp[5];
      cdsRegC405GT_FIN.Value := vTemp[6];
      cdsRegC405VL_BRT.Value := vTemp[7];
      cdsRegC405ECF_CX.Value := vCx;
      cdsRegC405.FieldByName('ID_REGC405').Value := vIDC405;
      cdsRegC405PONTO_DE_VENDA.Value := cdsRegC400PONTO_DE_VENDA.Value;
    end
    else
    if vTemp[1] = 'C420' then
    begin
      cdsRegC420.Insert;
      cdsRegC420COD_TOT_PAR.Value := vTemp[2];
      cdsRegC420VLR_ACUM_TOT.Value := vTemp[3];
      cdsRegC420NR_TOT.Value := vTemp[4];
      cdsRegC420DESCR_NR_TOT.Value := vTemp[5];
      cdsRegC420ID_REGC405.Value := vIDC405;
      if cdsCodSit.Locate('COD_SIT_TXT',cdsRegC420COD_TOT_PAR.Value,[loCaseInsensitive]) then
        cdsRegC420COD_SIT_TGA.Value := cdsCodSitCOD_SIT_TGA.Value;

    end;
  end;

  vTemp.Free;
  vArquivo.Free;
  vRegC400.Free;
  vRegC405.Free;
  vRegC420.Free;

end;

function TFrmImportaReducao.ObterCancelamento: Double;
var vCancelamento : Double;
begin
  cdsRegC420.Filtered := False;
  cdsRegC420.Filter := 'ID_REGC405 = ' + IntToStr(cdsRegC405.FieldByName('ID_REGC405').Value);
  cdsRegC420.Filtered := True;
  if cdsRegC420.Locate('COD_TOT_PAR','Can-T',[loCaseInsensitive]) then vCancelamento := StrToFloat(cdsRegC420VLR_ACUM_TOT.Value)
  else
  vCancelamento := 0;
  Result := vCancelamento;
  cdsRegC420.Filtered := False;
  cdsRegC420.Filter := '';
end;

function TFrmImportaReducao.ObterCodReducao: Integer;
begin
  UniQuery1.Open;
  Result:= UniQuery1.FieldByName('RESULTADO').AsInteger;
  UniQuery1.Close;
end;

function TFrmImportaReducao.ObterCODSIT: string;
begin
  cdsCodSit.First;
  while not cdsCodSit.Eof do
  begin
    if cdsRegC420COD_TOT_PAR.Value = cdsCodSitCOD_SIT_TXT.Value then
    begin
     // ShowMessage(cdsRegC420COD_TOT_PAR.Value + '      ' + cdsCodSitCOD_SIT_TGA.Value);
      Result := cdsCodSitCOD_SIT_TGA.Value;
      Exit;
    end
    else
    begin
      Result := '';
      cdsCodSit.Next;
    end;

  end;
end;

function TFrmImportaReducao.ObterDesconto: Double;
var vDesconto : Double;
begin
  cdsRegC420.Filtered := False;
  cdsRegC420.Filter := 'ID_REGC405 = ' + IntToStr(cdsRegC405.FieldByName('ID_REGC405').Value);
  cdsRegC420.Filtered := True;
  if cdsRegC420.Locate('COD_TOT_PAR','DT',[loCaseInsensitive]) then vDesconto := StrToFloat(cdsRegC420VLR_ACUM_TOT.Value)
  else
  vDesconto := 0;
  Result := vDesconto;
  cdsRegC420.Filtered := False;
  cdsRegC420.Filter := '';
end;

function TFrmImportaReducao.ObterValorLiquido: Double;
var vDesconto , vCancelamento , vBruto : double;
begin
  cdsRegC420.Filtered := False;
  cdsRegC420.Filter := 'ID_REGC405 = ' + IntToStr(cdsRegC405.FieldByName('ID_REGC405').Value);
  cdsRegC420.Filtered := True;
  if cdsRegC420.Locate('COD_TOT_PAR','Can-T',[loCaseInsensitive]) then vCancelamento := StrToFloat(cdsRegC420VLR_ACUM_TOT.Value);
  if cdsRegC420.Locate('COD_TOT_PAR','DT',[loCaseInsensitive]) then vDesconto := StrToFloat(cdsRegC420VLR_ACUM_TOT.Value);
  vBruto := StrToFloat(cdsRegC405VL_BRT.Value);
  Result := vBruto - vDesconto - vCancelamento;
  cdsRegC420.Filtered := False;
  cdsRegC420.Filter := '';
end;

procedure TFrmImportaReducao.SalvarConfigs;
var PathExe : string;
begin
  PathExe := ExtractFilePath(ParamStr(0));
  cdsCodSit.SaveToFile(PathExe + 'cdsCodSit.cds',dfBinary);
  cdsPontoDeVenda.SaveToFile(PathExe + 'cdsPontoDeVenda.cds',dfBinary);
end;

end.
